// 让node能连接mysql
const mysql = require('mysql')  //引入mysql包  npm install mysql
const config = require('../config/index.js')

// 创建线程池
const pool = mysql.createPool({
  host: config.database.HOST,
  user: config.database.USERNAME,
  password: config.database.PASSWORD,
  database: config.database.DATABASE,
  port: config.database.PORT
})

// 首先通过pool.getConnection()方法获取数据库连接。

// 这段代码是一个JavaScript对象，名为allService。它包含一个名为query的方法，该方法接受两个参数：sql和values。query方法返回一个Promise对象，用于异步执行数据库查询操作。
// 在Promise的回调函数中，首先通过pool.getConnection()方法获取数据库连接。如果连接过程中发生错误，将调用reject()
// 方法并传递错误对象。如果连接成功，则使用connection.query()方法执行传入的SQL语句，并将结果传递给回调函数。
// 在回调函数中，如果执行SQL语句时发生错误，同样会调用reject()方法并传递错误对象。如果SQL语句执行成功，则调用resolve()方法并传递查询结果。最后，无论查询是否成功，都会调用connection.release()方法来释放数据库连接。
// 这段代码的作用是提供一个异步的数据库查询服务，可以通过调用allService.query(sql, values)方法来执行SQL查询，并返回一个Promise对象，以便在查询完成后进行后续处理。

// 连接数据库，执行sql语句
const allService = {
  query: function (sql, values) {  //query 为一个方法
    return new Promise((resolve, reject) => {
      // 分别是错误对象 err 和表示数据库连接的 connection 对象
      pool.getConnection((err, connection) => {  //连接对象
        if (err) {
          reject(err)
        } else {
          // 回调函数通常会携带三个参数：error 表示执行过程中发生的错误，results 表示查询结果，fields 表示查询返回的字段信息。
          connection.query(sql, values, (error, rows) => {
            // 处理查询结果或错误
            if (err) {
              reject(err)
            } else { // sql语句执行成功
              resolve(rows)
            }
            connection.release() //断开连接
          });
        }
      })
    })
  }
}


// 登录
const userLogin = (username, password) => {
  let _sql = `select * from user where userName="${username}" and password="${password}";`
  return allService.query(_sql)
}

// 注册
const userRegister = (values) => {
  let _sql = `insert into user set userName=?,password=?,phone=?;`
  return allService.query(_sql, values)
}

// 注册用户查询是否存在已经注册过
const userSearch = (phone) => {
  let _sql = `select * from user where phone="${phone}";`
  return allService.query(_sql)
}


// 给出表单数据
const giveFormData = () => {
  let _sql = `select * from menu;`
  return allService.query(_sql)
}

// 获取数据库user中的注释
const getAnnotations = () => {
  // let _sql = `select *  from user;`
  let _sql = `SHOW FULL COLUMNS FROM user;`
  return allService.query(_sql)
}

// 获取user中数据表
const getUserList = () => {
  let _sql = `select * from user;`
  return allService.query(_sql)
}


// 通过id获取具体的user信息
const getUserFromId = (user_id) => {
  let _sql = `select * from user where user_id="${user_id}";`
  return allService.query(_sql)
}

// 修改用户的信息
const changeUserData = (userName, password, phone, userType, sex, avatar, points, userid) => {
  let _sql = `UPDATE user SET userName=?, password=?, phone=?, userType=?, sex=?, avatar=?, points=? WHERE user_id =?;`
  let values = [userName, password, phone, userType, sex, avatar, points, userid]
  return allService.query(_sql, values);
}

// 增加用户的信息
const addUserInformation = (values) => {
  let _sql = `insert into user set userName=?,password=?,phone=?,userType=?,sex=?,avatar=?,points=?;`
  return allService.query(_sql, values)
}

// 查找用户的名称
const findName = (searchName, selectioned) => {
  let _sql = 'select * from user';
  let values = [];
  if (searchName && selectioned) {
    _sql += ` where userName LIKE ? and userType = ?;`;
    values = [`%${searchName}%`, selectioned];
  } else if (selectioned) {
    _sql += ` where userType = ?;`;
    values = [selectioned];
  } else if (searchName) {
    _sql += ` where userName LIKE ?;`;
    values = [`%${searchName}%`];
  } else {
    _sql += ';';
  }

  return allService.query(_sql, values);
}

// 删除用户的信息
const deleteUser = (userid) => {
  let _sql = `DELETE FROM user WHERE user_id = ${userid};`;
  return allService.query(_sql)
}


// 获取商品中的数据
const findProduct = () => {
  let _sql = `select * from product;`
  return allService.query(_sql)
}

// 获取数据库商品中的注释
const getProductAnnotations = () => {
  // let _sql = `select *  from user;`
  let _sql = `SHOW FULL COLUMNS FROM product;`
  return allService.query(_sql)
}


// 增加商品
const addProduct = (values) => {
  let _sql = `insert into product set product_name=?,image_url=?,description=?,price=?,weight=?,inventory=?,category_id=?,created_at=?;`
  return allService.query(_sql, values)
}

// 修改商品
const changeProduct = (product_name, image_url, description, price, weight, inventory, category_id, updated_at, product_id) => {
  let _sql = `update product set product_name=?, image_url=?, description=?, price=?, weight=?, inventory=?, category_id=?,updated_at=? where product_id =?;`
  let values = [product_name, image_url, description, price, weight, inventory, category_id, updated_at, product_id];
  return allService.query(_sql, values)
}

// 删除商品
const deleteProduct = (product_id) => {
  let _sql = `delete from product where product_id=?;`
  let values = [product_id]
  return allService.query(_sql, values)
}

// 搜索商品,通过类别名，或者商品名
const searchProduct = (category_id, product_name) => {
  let _sql = 'select * from product';
  let values = [];
  if (category_id && product_name) {
    _sql += ` where product_name LIKE ? and category_id = ?;`; // 使用LIKE进行模糊查找
    values = [`%${product_name}%`, category_id]; // 正确构建参数数组
  } else if (category_id) {
    _sql += ` where category_id = ?;`;
    values = [category_id];
  } else if (product_name) {
    _sql += ` where product_name LIKE ?;`; // 使用LIKE进行模糊查找
    values = [`%${product_name}%`]; // 正确构建参数数组
  } else {
    _sql += ';';
  }
  return allService.query(_sql, values);
}

// 获取商品中的类别
const getCategory = () => {
  let _sql = `select * from category;`
  return allService.query(_sql)
}

// 获取类别的注释
const getCategoryAnnotations = () => {
  let _sql = `SHOW FULL COLUMNS FROM category;`
  return allService.query(_sql)
}

// 添加类别
const addCategory = (values) => {
  let _sql = `insert into category set category_name=?,description=?,created_at=?;`
  return allService.query(_sql, values)
}

// 修改类别
const changeCategory = (category_name, description, updated_at, category_id) => {
  let _sql = `update category set category_name=?, description=?, updated_at=? where category_id = ?;`
  let values = [category_name, description, updated_at, category_id];
  return allService.query(_sql, values);
}

// 删除类别
const deleteCategory = (category_id) => {
  let _sql = `delete from category where category_id=?;`
  let values = [category_id]
  return allService.query(_sql, values)
}

// 查找类别
const searchCategory = (category_name) => {
  let _sql = 'select * from category  where category_name LIKE ?';
  let values = [`%${category_name}%`];
  return allService.query(_sql, values);
}

// 获取订单
const getOrder = () => {
  let _sql = `select * from orders`;
  return allService.query(_sql)
}

// 获取订单的注释
const getOrderAnnotations = () => {
  let _sql = `SHOW FULL COLUMNS FROM orders;`
  return allService.query(_sql)
}

// 删除订单的数据
const deleteOrder = (order_id) => {
  let _sql = `delete from orders where order_id=?;`
  let values = [order_id]
  return allService.query(_sql, values)
}

// 查找订单
const searchOrder = (order_id, status) => {
  let _sql = 'select * from orders';
  let values = [];
  if (order_id && status) {
    _sql += ` where order_id LIKE ? and status LIKE ?`;
    values = [`%${order_id}%`, `%${status}%`];
  } else if (status) {
    _sql += ` where status LIKE ?`;
    values = [`%${status}%`];
  } else if (order_id) {
    _sql += ` where order_id LIKE ?`;
    values = [`%${order_id}%`];
  }
  _sql += ';';

  return allService.query(_sql, values);
};

// 获取轮播图数据
const gettingRotating = () => {
  let _sql = `select * from rotating;`
  return allService.query(_sql)
}

// 获取轮播图数据名
const gettingRotatingAnnotations = () => {
  let _sql = `SHOW FULL COLUMNS FROM rotating;`
  return allService.query(_sql)
}

// 添加轮播图数据
const addRotating = (values) => {
  let _sql = `insert into rotating set image_url=?,target_url=?,created_at=?;`
  return allService.query(_sql, values)
}

// 修改轮播图数据
const changeRotating = (image_url, target_url, updated_at, rotate_id) => {
  let _sql = `update rotating set image_url=?, target_url=?, updated_at=? where rotate_id = ?;`
  let values = [image_url, target_url, updated_at, rotate_id];
  return allService.query(_sql, values);
}

// 删除轮播图数据
const deleteRotating = (rotate_id) => {
  let _sql = `delete from rotating where rotate_id=?;`
  let values = [rotate_id]
  return allService.query(_sql, values)
}


// 获取活动数据
const getEvent = () => {
  let _sql = `select * from event;`
  return allService.query(_sql)
}

// 获取活动数据表名字
const getEventAnnotations = () => {
  let _sql = `SHOW FULL COLUMNS FROM event;`
  return allService.query(_sql)
}

// 添加活动数据
const addEvent = (values) => {
  let _sql = `insert into event set event_name=?,description=?,image_url=?,start_date=?,end_date=?,created_at=?;`
  return allService.query(_sql, values)
}

// 修改活动数据
const changeEvent = (event_name, description, image_url, start_date, end_date, updated_at, event_id) => {
  let _sql = `update event set event_name=?, description=?, image_url=?, start_date=?, end_date=?, updated_at=? where event_id = ?;`
  let values = [event_name, description, image_url, start_date, end_date, updated_at, event_id];
  return allService.query(_sql, values);
}

// 删除活动数据
const deleteEvent = (event_id) => {
  let _sql = `delete from event where event_id=?;`
  let values = [event_id]
  return allService.query(_sql, values)
}


// 获取公告数据
const getBulletin = () => {
  let _sql = `select * from bulletin;`
  return allService.query(_sql)
}

// 获取公告数据表名字
const getBulletinAnnotations = () => {
  let _sql = `SHOW FULL COLUMNS FROM bulletin;`
  return allService.query(_sql)
}

// 增加公告数据
const addBulletin = (values) => {
  let _sql = `insert into bulletin set bulletin_name=?,bulletin_content=?,created_at=?;`
  return allService.query(_sql, values)
}

// 删除公告数据
const deleteBulletin = (bulletin_id) => {
  let _sql = `delete from bulletin where bulletin_id=?;`
  let values = [bulletin_id]
  return allService.query(_sql, values)
}

// 获取上架商品
const getShelving = () => {
  let _sql = `select * from product where is_listed=1;`
  return allService.query(_sql)
}

// 获取推荐的商品
const getRecommended = () => {
  let _sql = `select * from product where recommended=1;`
  return allService.query(_sql)
}

// 改变商品的上下架，推荐
const changeProductStatus = (product_id, is_listed, recommended) => {
  let _sql = ``
  let idList = Array.isArray(product_id) ? product_id.join(',') : product_id;
  _sql = `UPDATE product SET is_listed = ${is_listed}, recommended = ${recommended} WHERE product_id IN (${idList})`;
  return allService.query(_sql);
}


// 通过在类别中获得商品数据
const getProductDataInCategory = (category_id) => {
  let _sql = `select * from product where category_id="${category_id}"and is_listed=1;`
  return allService.query(_sql)
}

// 通过product_id 查询具体商品数据
const getProductDataFromId = (product_id) => {
  let _sql = `select * from product where product_id="${product_id}"`
  return allService.query(_sql)
}

// 添加购物车
const addShoppingCart = (values) => {
  let _sql = `insert into shoppingCart set user_id=?,product_id=?,quantity=?,price=?,image_url=?,description=?,product_name=?,inventory=?;`
  return allService.query(_sql, values)
}

// 商品是否已经存在购物车
const isInShoppingCart = (product_id) => {
  let _sql = `select * from shoppingCart where product_id="${product_id}";`
  return allService.query(_sql)
}

// 获取购物车信息
const getShoppingCart = (user_id) => {
  let _sql = `select * from shoppingCart where user_id="${user_id}";`
  return allService.query(_sql)
}

// 更改商品数量永久存储
const changeQuantityInShopping = (quantity, id) => {
  let _sql = `update shoppingcart set quantity=? where id = ?;`
  let values = [quantity, id];
  return allService.query(_sql, values);
}

// 删除购物车数据通过id
const deleteShoppingData = (id) => {
  let _sql = `delete from shoppingcart where id=?;`
  let values = [id]
  return allService.query(_sql, values)
}

// 通过id 查询具体购物车数据
const getShoppingDataFromId = (id) => {
  let _sql = `select * from shoppingcart where id IN (${id.join(',')})`;
  return allService.query(_sql);
}

// 获取个人的所有的收获地址
const getCustomersAddress = (user_id) => {
  let _sql = `select * from customersaddress where user_id="${user_id}";`
  return allService.query(_sql)
}

// 删除个人的某个的收获地址
const deleteCustomersAddress = (id) => {
  let _sql = `delete from customersaddress where id=?;`
  let values = [id]
  return allService.query(_sql, values)
}

// 添加个人的某些的收获地址
const addCustomersAddress = (values) => {
  let _sql = `insert into customersaddress set name=?,user_id=?,address=?,phone_number=?;`
  return allService.query(_sql, values)
}

// 添加地址到个人的收获地址
const addCustomersAddressInUser = (address_id, user_id) => {
  let _sql = `update user set address_id=? where user_id=?;`
  let values = [user_id, address_id];
  return allService.query(_sql, values);
}

// 通过address_id获取详细地址
const getShoppingAddressFromId = (address_id) => {
  let _sql = `select * from customersaddress where id="${address_id}"`;
  return allService.query(_sql);
}

// 添加订单到orderItems附表，记录买了几件物品
const addOrderItemsSchedule = (values) => {
  let _sql = `insert into orderitems set order_id=?,product_id=?,quantity=?,unit_price=?,subtotal_price=?,product_name=?,product_image=?,product_description=?,created_at=?;`
  return allService.query(_sql, values)
}

// 添加订单到orders
const addOrders = (values) => {
  let _sql = `insert into orders set shipping_address=?,phone=?,name=?,user_id=?,total_amount=?,created_at=?,status=?;`
  return allService.query(_sql, values)
  let getLastOrderIdSql = `select last_insert_id() as order_id;`;
  let getLastOrderId = allService.query(getLastOrderIdSql);

  return Promise.all([insertSql, getLastOrderId])
    .then(([insertResult, lastOrderIdResult]) => {
      const lastOrderId = lastOrderIdResult[0].order_id;
      return { insertResult, lastOrderId };
    });
}

// 查询我的订单通过
const getOrderFromId = (user_id) => {
  let _sql = `select * from orders where user_id="${user_id}"`;
  return allService.query(_sql);
}

// 查询订单信息通过order_id
const getOrderUseOrderId= (order_id) => {
  let _sql = `select * from orders where order_id="${order_id}"`;
  return allService.query(_sql);
}

// 查询具体商品通过order_id
const getOrderFromOrderId = (order_id) => {
  let _sql = `select * from orderitems where order_id="${order_id}"`;
  return allService.query(_sql);
}

// 修改订单的状态
const changeOrderStatus = (status, order_id) => {
  let _sql = `update orders set status=? where order_id=?;`
  let values = [status, order_id];
  return allService.query(_sql, values);
}

// 当购买商品成功时修改商品的库存
// UPDATE product SET quantity = quantity - [购买数量] WHERE product_id = [物品ID];
const changeShoppingInventory = (inventory, product_id) => {
  let _sql = `UPDATE product SET inventory=inventory-? WHERE product_id =?;`
  let values = [inventory, product_id];
  return allService.query(_sql, values);
}

// 当取消订单时，修改库存
const changeCancelShoppingInventory = (inventory, product_id) => {
  let _sql = `UPDATE product SET inventory=inventory+? WHERE product_id =?;`
  let values = [inventory, product_id];
  return allService.query(_sql, values);
}

// 统计表管理
const showCountList=()=>{
  let _sql=`select * from orders where status="已完成"`;
  return allService.query(_sql)
}


// 根据order_id 在完成的订单中查询这个商品购置项，是否具有要查的名字


// 查询商品的销售量，在统计管理里面
const serachShoppingCount=(product_name)=>{
  let _sql=`select * from orderitems where product_name="${product_name}"`;
  return allService.query(_sql)
}





module.exports = {
  userLogin,
  userRegister,
  userSearch,
  giveFormData,
  getAnnotations,
  getUserList,
  changeUserData,
  addUserInformation,
  findName,
  deleteUser,
  findProduct,
  getProductAnnotations,
  getCategory,
  getCategoryAnnotations,
  addProduct,
  changeProduct,
  deleteProduct,
  searchProduct,
  addCategory,
  changeCategory,
  deleteCategory,
  searchCategory,
  getOrder,
  getOrderAnnotations,
  deleteOrder,
  searchOrder,
  gettingRotating,
  gettingRotatingAnnotations,
  addRotating,
  deleteRotating,
  changeRotating,
  getEvent,
  getEventAnnotations,
  getBulletin,
  getBulletinAnnotations,
  addEvent,
  deleteEvent,
  changeEvent,
  addBulletin,
  deleteBulletin,
  getShelving,
  changeProductStatus,
  getRecommended,
  getProductDataInCategory,
  getProductDataFromId,
  getUserFromId,
  addShoppingCart,
  isInShoppingCart,
  getShoppingCart,
  changeQuantityInShopping,
  deleteShoppingData,
  getShoppingDataFromId,
  getCustomersAddress,
  deleteCustomersAddress,
  addCustomersAddress,
  addCustomersAddressInUser,
  getShoppingAddressFromId,
  addOrderItemsSchedule,
  addOrders,
  getOrderFromId,
  getOrderFromOrderId,
  changeOrderStatus,
  changeShoppingInventory,
  getOrderUseOrderId,
  changeCancelShoppingInventory,
  showCountList,
  serachShoppingCount
}